package com.zjs.express.dao.impl;

import com.zjs.express.bean.Express;
import com.zjs.express.bean.vo.ExpressCountVo;
import com.zjs.express.bean.vo.UserCountVo;
import com.zjs.express.common.ResultModel;
import com.zjs.express.dao.ExpressDao;
import com.zjs.express.utils.DBUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ExpressDaoImpl extends DBUtils implements ExpressDao {
    @Override
    public ExpressCountVo statistics() {
        String sql = "SELECT COUNT(*) AS expressTotalCount, COUNT(to_days(in_time)=to_days(now()) or null) AS expressDayRegCount, COUNT(status=0 or null) AS pendingPickupCount, COUNT(to_days(in_time)=to_days(now()) and status=0 or null) AS expressAddCount FROM express";
        try {
            ResultSet resultSet = query(sql, null);
            while (resultSet.next()) {
                return new ExpressCountVo(resultSet.getInt("expressTotalCount"), resultSet.getInt("expressDayRegCount"), resultSet.getInt("pendingPickupCount"), resultSet.getInt("expressAddCount"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return null;
    }

    @Override
    public List<Express> list(Integer pageIndex, Integer pageSize) {
        String sql = "SELECT * FROM express ORDER BY in_time DESC LIMIT ?,?";
        ResultSet resultSet = query(sql, new Object[]{pageIndex, pageSize});
        List<Express> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                Express express = new Express();
                express.setId(resultSet.getString("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUsername(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("user_phone"));
                express.setCompany(resultSet.getString("company"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getDate("in_time"));
                express.setOutTime(resultSet.getDate("out_time"));
                express.setStatus(Integer.valueOf(resultSet.getString("status")));
                express.setSysPhone(resultSet.getString("sys_phone"));
                list.add(express);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return list;
    }

    @Override
    public Integer getTotal() {
        String sql = "SELECT COUNT(*) AS total FROM express";
        ResultSet resultSet = query(sql, null);
        try {
            while (resultSet.next()) {
                return resultSet.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return 0;
    }

    @Override
    public boolean add(Express express) {
        String sql = "INSERT INTO express(number, username, user_phone, company, code, in_time, status, sys_phone) VALUES (?,?,?,?,?,NOW(),?,?)";
        boolean insert = insert(sql, new Object[]{express.getNumber(), express.getUsername(), express.getUserPhone(), express.getCompany(), express.getCode(), express.getStatus(), express.getSysPhone()});
        if (insert) {
            return true;
        }
        return false;
    }

    @Override
    public Express listByNumber(String number) {
        String sql = "SELECT * FROM express where number=?";
        ResultSet resultSet = query(sql, new Object[]{number});
        Express express = null;
        try {
            while (resultSet.next()) {
                express = new Express();
                express.setId(resultSet.getString("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUsername(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("user_phone"));
                express.setCompany(resultSet.getString("company"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getDate("in_time"));
                express.setOutTime(resultSet.getDate("out_time"));
                express.setStatus(Integer.valueOf(resultSet.getString("status")));
                express.setSysPhone(resultSet.getString("sys_phone"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return express;
    }

    @Override
    public boolean update(Express express) {
        String sql = "UPDATE express SET number=?, username=?, user_phone=?, company=?, status=?, sys_phone=? WHERE id=?";
        boolean update = update(sql, new Object[]{express.getNumber(), express.getUsername(), express.getUserPhone(), express.getCompany(), express.getStatus(), express.getSysPhone(), express.getId()});
        if (update) {
            return true;
        }
        return false;
    }
}
